package ca.llsutherland.squash.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import ca.llsutherland.squash.domain.Administrator;
import ca.llsutherland.squash.domain.Clock;
import ca.llsutherland.squash.domain.Role;
import ca.llsutherland.squash.domain.helper.AdministratorsHelper;
import ca.llsutherland.squash.exceptions.ValidationException;
import ca.llsutherland.squash.utils.StringUtils;

public class AdministratorsDao extends BaseDao {

    private static AdministratorsDao instance = new AdministratorsDao();

    @Override
    public String getUnderlyingTableName() {
        return "ADMINISTRATORS";
    }

    public static AdministratorsDao getInstance() {
        return instance ;
    }

    public Administrator create(Administrator administrator) {
        String sql = "INSERT INTO " + getUnderlyingTableName() + " (FIRST_NAME, LAST_NAME, DATE_CREATED, DATE_UPDATED, PASSWORD, ROLE_ID) VALUES (" 
            + StringUtils.addSingleQuotes(administrator.getFirstname()) + ", "
            + StringUtils.addSingleQuotes(administrator.getLastname()) + ", "
            + StringUtils.addSingleQuotes(administrator.getDateCreated().toString()) + ", "
            + StringUtils.addSingleQuotes(administrator.getDateCreated().toString()) + ", "
            + StringUtils.addSingleQuotes(administrator.getPassword()) + ", "
            + administrator.getRole().getId() + ");";
        return findById(super.create(sql));
    }

    public Administrator findById(Long id) {
        String findByIdQuery = getFindByIdQuery(id);
        return buildAdministrator(findByIdQuery);
    }

    private Administrator buildAdministrator(String sql) {
        Administrator administrator = null;
        ResultSet rs = super.findByQuery(sql);
        
        try {
            if(rs.next()){
                String firstname = rs.getString("FIRST_NAME");
                String lastname = rs.getString("LAST_NAME");
                String password = rs.getString("PASSWORD");
                Clock dateCreated = new Clock(rs.getTimestamp("DATE_CREATED").getTime());
                Clock dateUpdated = new Clock(rs.getTimestamp("DATE_UPDATED").getTime());
                Long id = Long.valueOf(rs.getLong("ID"));
                Role role = RolesDao.getInstance().findById(rs.getLong("ROLE_ID"));
                
                administrator = AdministratorsHelper.createNewAdministrator(firstname, lastname, dateCreated, dateUpdated, password, role, id);
            }
            rs.close();
        } catch (SQLException e) {
            throw new ValidationException(e.getMessage() + " in buildAdministrator()");
        } finally {
            rs = null;            
        }
        return administrator;
    }

    public Administrator findByFirstnameLastnamePassword(String firstname, String lastname, String password) {
        String sql = "SELECT * FROM " + getUnderlyingTableName() + " WHERE FIRST_NAME = "
            + StringUtils.addSingleQuotes(firstname) + " AND "
            + StringUtils.addSingleQuotes(lastname) + " AND "
            + StringUtils.addSingleQuotes(password) + ";";
        return buildAdministrator(sql);
    }

}
